﻿using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;
using System;

namespace Shuba.Data
{
	public class Branch
	{
		public Branch()
		{
		}

		public List<BranchInfo> GetList(string value)
		{
			List<BranchInfo> list = new List<BranchInfo>();
			Database db = DBConnection.CreateDatabase();
			string sql;
			DbCommand cmd;

            sql = @"SELECT BRANCH_ID, SOURCE_CODE, BRANCH_MAPPING, BRANCH_NAME, PHONE_NO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE 
                    FROM TBL_MS_BRANCH WHERE (BRANCH_NAME LIKE '%" + value + "%' OR BRANCH_ID LIKE '%" + value + "%') ORDER BY BRANCH_NAME";
			cmd = db.GetSqlStringCommand(sql);
			using (IDataReader dr = db.ExecuteReader(cmd))
			{
				while (dr.Read())
				{
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
				}
                dr.Close();
			}
			return list;
		}

        public List<BranchInfo> GetListByBranchID(string value)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;

            sql = @"SELECT BRANCH_ID, SOURCE_CODE, BRANCH_MAPPING, BRANCH_NAME, PHONE_NO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE 
                   FROM TBL_MS_BRANCH WHERE BRANCH_ID LIKE '" + value + "' ORDER BY BRANCH_NAME";
            cmd = db.GetSqlStringCommand(sql);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchInfo> GetListByRegionAndCluster(string RegionID, string ClusterID)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"
            SELECT * FROM 
            (
                SELECT A.BRANCH_ID, A.SOURCE_CODE, A.BRANCH_MAPPING, A.BRANCH_NAME, A.PHONE_NO, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE 
                FROM TBL_MS_BRANCH A 
                INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID 
                INNER JOIN TBL_MS_REGION_CLUSTER C ON B.CLUSTER_ID = C.CLUSTER_ID 
                WHERE 
                    (C.REGIONAL_ID LIKE '' + @REGIONAL_ID + '') 
                    AND (B.CLUSTER_ID LIKE '' + @CLUSTER_ID + '')                            
                
            ) X ORDER BY X.BRANCH_NAME";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionID);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchInfo> GetListByRegionAndCluster(string RegionID, string ClusterID, string Nip, string AppID)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"DECLARE @PROCESS_DATE DATETIME, @COST_CENTER VARCHAR(6), @USER_TYPE VARCHAR(10) 
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

            SELECT TOP 1 @COST_CENTER = BRANCH_ID FROM V_USER 
            WHERE NIP = @NIP AND APP_ID = @APP_ID GROUP BY BRANCH_ID

            SELECT TOP 1 @USER_TYPE = USER_TYPE FROM V_USER
            WHERE NIP = @NIP AND APP_ID = @APP_ID GROUP BY USER_TYPE

            IF (@USER_TYPE = 'LA' OR @USER_TYPE = 'CO' OR @USER_TYPE = 'SO')
            BEGIN
                SELECT D.BRANCH_ID, D.SOURCE_CODE, D.BRANCH_MAPPING, D.BRANCH_NAME, D.PHONE_NO, D.ENTRY_BY, D.ENTRY_DATE, D.UPDATE_BY, D.UPDATE_DATE
                FROM TBL_ALTERNATE_UM A INNER JOIN TBL_MS_CLUSTER_BRANCH B 
                ON A.BRANCH_ID = B.BRANCH_ID INNER JOIN TBL_MS_REGION_CLUSTER C 
                ON B.CLUSTER_ID = C.CLUSTER_ID INNER JOIN TBL_MS_BRANCH D 
                ON A.BRANCH_ID = D.BRANCH_ID
                WHERE
                    (C.REGIONAL_ID LIKE '' + @REGIONAL_ID + '') AND (C.CLUSTER_ID LIKE '' + @CLUSTER_ID + '') 
                    AND (A.NIP = @NIP)
			        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),A.EFF_DATE_FROM,120)) 
			        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),A.EFF_DATE_TO,120)) 
			        AND A.IS_ACTIVE = '1'
            END
            
            ELSE

            SELECT * FROM 
            (
                SELECT A.BRANCH_ID, A.SOURCE_CODE, A.BRANCH_MAPPING, A.BRANCH_NAME, A.PHONE_NO, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE 
                FROM TBL_MS_BRANCH A INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID 
                INNER JOIN TBL_MS_REGION_CLUSTER C ON B.CLUSTER_ID = C.CLUSTER_ID 
                INNER JOIN 
                (
                    SELECT C.BRANCH_ID                            
                    FROM TBL_MS_CLUSTER_BRANCH AS C INNER JOIN 
                    TBL_MS_REGION_CLUSTER AS A ON A.CLUSTER_ID = C.CLUSTER_ID LEFT JOIN 
                    TBL_MS_HO_REGION AS B ON A.REGIONAL_ID = B.REGIONAL_ID 
                    WHERE
                        (C.BRANCH_ID = @COST_CENTER) OR 
                        (B.HO_ID = @COST_CENTER) OR 
                        (A.REGIONAL_ID = @COST_CENTER) OR 
                        (C.CLUSTER_ID = @COST_CENTER)
                    GROUP BY C.BRANCH_ID
                 ) CC ON A.BRANCH_ID = CC.BRANCH_ID
                WHERE 
                    (C.REGIONAL_ID LIKE '' + @REGIONAL_ID + '') 
                    AND (B.CLUSTER_ID LIKE '' + @CLUSTER_ID + '')                            
                UNION
                SELECT D.BRANCH_ID, D.SOURCE_CODE, D.BRANCH_MAPPING, D.BRANCH_NAME, D.PHONE_NO, D.ENTRY_BY, D.ENTRY_DATE, D.UPDATE_BY, D.UPDATE_DATE
                FROM TBL_ALTERNATE_UM A INNER JOIN TBL_MS_CLUSTER_BRANCH B 
                ON A.BRANCH_ID = B.BRANCH_ID INNER JOIN TBL_MS_REGION_CLUSTER C 
                ON B.CLUSTER_ID = C.CLUSTER_ID INNER JOIN TBL_MS_BRANCH D 
                ON A.BRANCH_ID = D.BRANCH_ID
                WHERE
                    (C.REGIONAL_ID LIKE '' + @REGIONAL_ID + '') AND (C.CLUSTER_ID LIKE '' + @CLUSTER_ID + '') 
                    AND (A.NIP = @NIP)
			        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),A.EFF_DATE_FROM,120)) 
			        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),A.EFF_DATE_TO,120)) 
			        AND A.IS_ACTIVE = '1'
            ) X ORDER BY X.BRANCH_NAME";
            
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionID);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterID); 
            db.AddInParameter(cmd, "@NIP", DbType.String, Nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchInfo> GetListByHO(string HO_ID, string REGIONAL_ID, string CLUSTER_ID)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.BRANCH_ID, A.SOURCE_CODE, A.BRANCH_MAPPING, 
                                                        A.BRANCH_NAME, A.PHONE_NO, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE 
                                                    FROM TBL_MS_BRANCH A 
                                                        INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID 
                                                        INNER JOIN TBL_MS_REGION_CLUSTER C ON B.CLUSTER_ID = C.CLUSTER_ID
                                                        INNER JOIN TBL_MS_HO_REGION D ON D.REGIONAL_ID = C.REGIONAL_ID
                                                    WHERE D.HO_ID = @HO_ID AND D.REGIONAL_ID LIKE '' + @REGIONAL_ID + '' AND C.CLUSTER_ID LIKE '' + @CLUSTER_ID + ''
                                                    ORDER BY A.BRANCH_NAME");
            db.AddInParameter(cmd, "@HO_ID", DbType.String, HO_ID);
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, REGIONAL_ID);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, CLUSTER_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchInfo> GetListByRegion(string REGIONAL_ID, string CLUSTER_ID)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.BRANCH_ID, A.SOURCE_CODE, A.BRANCH_MAPPING, 
                                                            A.BRANCH_NAME, A.PHONE_NO, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE 
                                                    FROM TBL_MS_BRANCH A 
                                                        INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID 
                                                        INNER JOIN TBL_MS_REGION_CLUSTER C ON B.CLUSTER_ID = C.CLUSTER_ID
                                                    WHERE C.REGIONAL_ID = @REGIONAL_ID AND C.CLUSTER_ID LIKE '' + @CLUSTER_ID + '' 
                                                    ORDER BY A.BRANCH_NAME");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, REGIONAL_ID);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, CLUSTER_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchInfo> GetListByCluster(string CLUSTER_ID)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.BRANCH_ID, A.SOURCE_CODE, A.BRANCH_MAPPING, 
                                                            A.BRANCH_NAME, A.PHONE_NO, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE 
                                                    FROM TBL_MS_BRANCH A 
                                                        INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID 
                                                        INNER JOIN TBL_MS_REGION_CLUSTER C ON B.CLUSTER_ID = C.CLUSTER_ID
                                                    WHERE B.CLUSTER_ID = @CLUSTER_ID
                                                    ORDER BY A.BRANCH_NAME");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, CLUSTER_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchInfo> GetListApproval(string value, string UpdateBy)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;

            sql = @"SELECT BRANCH_ID, SOURCE_CODE, BRANCH_MAPPING, BRANCH_NAME, PHONE_NO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE 
                    FROM TBL_MS_BRANCH_TMP WHERE (BRANCH_NAME LIKE '%" + value + "%' OR BRANCH_ID LIKE '%" + value + "%') AND (RTRIM(UPDATE_BY) <> '" + UpdateBy + "') ORDER BY UPDATE_DATE DESC";
            cmd = db.GetSqlStringCommand(sql);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchInfo> GetDependency(string BranchID)
        {
            List<BranchInfo> list = new List<BranchInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT DISTINCT * FROM
                                (
                                    SELECT TOP 1 A.* FROM TBL_MS_BRANCH A INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
                                    WHERE A.BRANCH_ID LIKE '' + @BRANCH_ID + ''
                                    UNION
                                    SELECT TOP 1 A.* FROM TBL_MS_BRANCH A INNER JOIN TBL_MS_BRANCH_ZIP B ON A.BRANCH_ID = B.BRANCH_ID
                                    WHERE A.BRANCH_ID LIKE '' + @BRANCH_ID + ''
                                ) X
                        ";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8)));
                }
                dr.Close();
            }
            return list;
        }
        
		public BranchInfo Get(string id)
		{
			BranchInfo bi = new BranchInfo();
			//Database db = DatabaseFactory.CreateDatabase();
			Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, SOURCE_CODE, BRANCH_MAPPING, BRANCH_NAME, PHONE_NO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE 
                                FROM TBL_MS_BRANCH WHERE BRANCH_ID LIKE '' + @BRANCH_ID + ''");
			db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
			using (IDataReader dr = db.ExecuteReader(cmd))
			{
				while (dr.Read())
				{
                    bi = new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8));
				}
                dr.Close();
			}

			return bi;
		}

        public BranchInfo GetApprovalByID(string id)
        {
            BranchInfo bi = new BranchInfo();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, SOURCE_CODE, BRANCH_MAPPING, BRANCH_NAME, PHONE_NO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE 
                                FROM TBL_MS_BRANCH_TMP WHERE BRANCH_ID LIKE '' + @BRANCH_ID + ''");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new BranchInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }

            return bi;
        }

        public void Save(BranchInfo branch)
		{
			//Database db = DatabaseFactory.CreateDatabase();
			Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
            	            UPDATE TBL_MS_BRANCH_TMP SET
            		            SOURCE_CODE = LEFT(@SOURCE_CODE,2), 
            		            BRANCH_MAPPING = LEFT(@BRANCH_MAPPING,5), 
            		            BRANCH_NAME = LEFT(@BRANCH_NAME,50), 
            		            PHONE_NO = LEFT(@PHONE_NO,12),
                                UPDATE_BY = @UPDATE_BY, 
                                UPDATE_DATE = @UPDATE_DATE, 
                                ACT_TYPE = @ACT_TYPE 
            	            WHERE BRANCH_ID LIKE '' + @BRANCH_ID + '';
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_MS_BRANCH_TMP(BRANCH_ID, SOURCE_CODE, BRANCH_MAPPING, BRANCH_NAME, PHONE_NO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE) 
                            VALUES(LEFT(@BRANCH_ID,6), LEFT(@SOURCE_CODE,2), LEFT(@BRANCH_MAPPING,5), LEFT(@BRANCH_NAME,50), LEFT(@PHONE_NO,12), @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE)
                            ");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branch.BranchID);
            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, branch.SourceCode);
            db.AddInParameter(cmd, "@BRANCH_MAPPING", DbType.String, branch.BranchMapping);
            db.AddInParameter(cmd, "@BRANCH_NAME", DbType.String, branch.BranchName);
            db.AddInParameter(cmd, "@PHONE_NO", DbType.String, branch.PhoneNo);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, branch.EntryBy);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, branch.EntryDate);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, branch.UpdateBy);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, branch.UpdateDate);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, branch.ActType);

            db.ExecuteNonQuery(cmd);
		}

        public void Approve(string BranchId, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            
            DbCommand cmd = db.GetStoredProcCommand("BRANCH_APPROVAL");

            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchId);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string BranchId)
        {
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();

            string sql = String.Format("DELETE FROM TBL_MS_BRANCH_TMP WHERE BRANCH_ID LIKE '{0}'", BranchId);
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.ExecuteNonQuery(cmd);
        }

	}
}